﻿Imports System.Collections.ObjectModel

Public Class RowModel

    ''' <summary>
    ''' Set/retrieve name of table to be modelled.
    ''' </summary>
    ''' <value>Table name</value>
    ''' <returns>Table name</returns>
    ''' <remarks></remarks>
    Public Property TableName As String

#Region "Collection"

    Private _andCollection As Collection(Of FieldModel) = New Collection(Of FieldModel)
    Private _orCollection As Collection(Of FieldModel) = New Collection(Of FieldModel)
    Private _setCollection As Collection(Of FieldModel) = New Collection(Of FieldModel)
    Private _selectCollection As Collection(Of String) = New Collection(Of String)
    Private _keyCollection As Collection(Of FieldModel) = New Collection(Of FieldModel)

    ''' <summary>
    ''' Return the collection of AND fields.
    ''' 
    ''' OR fields are normally used for modelling WHERE clause in SQL SELECT/UPDATE/DELETE statement construction.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property AndCollection() As Collection(Of FieldModel)
        Get
            Return _andCollection
        End Get
    End Property

    ''' <summary>
    ''' Return the collection of OR fields.
    ''' 
    ''' OR fields are normally used for modelling WHERE clause in SQL SELECT/UPDATE/DELETE statement construction.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property OrCollection() As Collection(Of FieldModel)
        Get
            Return _orCollection
        End Get
    End Property

    ''' <summary>
    ''' Return the collection of Key fields.
    ''' 
    ''' KEY fields are normally used in modelling WHERE clause in SQL UPDATE/DELETE statement construction.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property KeyCollection() As Collection(Of FieldModel)
        Get
            Return _keyCollection
        End Get
    End Property

    ''' <summary>
    ''' Return the collection of SET fields. 
    ''' 
    ''' SET fields are normally used in SQL UPDATE statement construction.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property SetCollection() As Collection(Of FieldModel)
        Get
            Return _setCollection
        End Get
    End Property

    ''' <summary>
    ''' Return the collection of SELECT fields.
    ''' 
    ''' SELECT fields are normally used for modelling the SELECT clause in SQL SELECT statement construction.
    ''' </summary>
    ''' <value></value>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property SelectCollection() As Collection(Of String)
        Get
            Return _selectCollection
        End Get
    End Property

#End Region

    ''' <summary>
    ''' Empty constructor.
    ''' </summary>
    ''' <remarks></remarks>
    Sub New(ByVal tableName As String)
        Me.TableName = tableName
    End Sub

    ''' <summary>
    ''' Clear all collections (AND/OR/SELECT/SET/KEY).
    ''' </summary>
    ''' <remarks></remarks>
    Sub Clear()
        Me.AndCollection.Clear()
        Me.OrCollection.Clear()
        Me.SetCollection.Clear()
        Me.SelectCollection.Clear()
        Me.KeyCollection.Clear()
    End Sub

#Region "Assign value to collection"

    ''' <summary>
    ''' Add/Replace AND condition to collection
    '''
    ''' - To negate a condition, add " NOT " as PREFIX to fieldname.
    ''' - For greater than comparison, add " >" as SUFFIX to fieldname, the assignment operation
    '''   will automatically append "=" to the fieldname, making it >=. Similar for less than.
    ''' </summary>
    ''' <param name="name">Name of field</param>
    ''' <param name="value">Value of field</param>
    ''' <remarks></remarks>
    Public Sub AndField(ByVal name As String, ByVal value As String)
        Dim tempField As FieldModel = Me.AndCollection.ToList.Find(Function(item) item.Name = name)
        If tempField IsNot Nothing Then
            tempField.FieldValue = value
        Else
            Me.AndCollection.Add(New FieldModel(name, value))
        End If
    End Sub

    ''' <summary>
    ''' Add/Replace OR condition to collection
    ''' </summary>
    ''' <param name="name">Field name.</param>
    ''' <param name="value">Field value.</param>
    ''' <remarks></remarks>
    Public Sub OrField(ByVal name As String, ByVal value As Object)
        Dim tempField As FieldModel = Me.OrCollection.ToList.Find(Function(item) item.Name = name)
        If tempField IsNot Nothing Then
            tempField.FieldValue = value
        Else
            Me.OrCollection.Add(New FieldModel(name, value))
        End If
    End Sub

    ''' <summary>
    ''' Add/Replace SET field value pair to collection.
    ''' </summary>
    ''' <param name="name">Name of field</param>
    ''' <param name="value">New value to be assigned to field</param>
    ''' <remarks></remarks>
    Public Sub SetField(ByVal name As String, ByVal value As Object)
        Dim tempField As FieldModel = Me.SetCollection.ToList.Find(Function(item) item.Name = name)
        If tempField IsNot Nothing Then
            tempField.FieldValue = value
        Else
            Me.SetCollection.Add(New FieldModel(name, value))
        End If
    End Sub

    ''' <summary>
    ''' Add/Replace Key field value pair to collection.
    ''' </summary>
    ''' <param name="name">Name of field</param>
    ''' <param name="value">New value to be assigned to field.</param>
    ''' <remarks></remarks>
    Public Sub KeyField(ByVal name As String, ByVal value As String)
        Dim tempField As FieldModel = KeyCollectionByName(name)
        If tempField IsNot Nothing Then
            tempField.FieldValue = value
        Else
            Me.KeyCollection.Add(New FieldModel(name, value))
        End If
    End Sub

    ''' <summary>
    ''' Add/Replace SELECT condition to collection
    ''' </summary>
    ''' <param name="name">Name of field to be included in SELECT statement</param>
    ''' <remarks></remarks>
    Public Sub SelectField(ByVal name As String)
        If Not Me.SelectCollection.Contains(name) Then
            Me.SelectCollection.Add(name)
        End If
    End Sub

#End Region

#Region "Select,Insert,Update string and keyfilter, recordfilter"

    ''' <summary>
    ''' Use SET field value collection to generate partial SQL string to be used in SQL insert statement.
    ''' This function use '(...) VALUES (...)' syntax in constructing
    ''' the insert statement (Mysql style).
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function InsertString() As String
        Dim valueList As String = ""
        Dim nameList As String = ""

        For Each field As FieldModel In Me.SetCollection
            nameList += field.Name + ","
            valueList += "'" + field.DataBaseValue + "', "
        Next
        valueList = valueList.Substring(0, valueList.Length - 1)

        Return "(" + nameList.Trim(",".ToCharArray) + ") VALUES (" + valueList.Trim(",".ToCharArray) + ")"
    End Function

    ''' <summary>
    ''' Use SET field value collection to generate SET clause of SQL UPDATE statement.
    ''' This function use 'SET field1=value1, field2=value2, ...' syntax (Mysql style).
    ''' </summary>
    ''' <returns>SET clause of SQL UPDATE statement</returns>
    ''' <remarks></remarks>
    Public Function UpdateString() As String
        Dim result As String = ""
        For Each field As FieldModel In Me.SetCollection
            ' Include in update string only if it's not a key field
            If KeyCollectionByName(field.Name) Is Nothing Then
                result += field.UpdateString + ","
            End If
        Next
        Return "SET " + result.Trim(",".ToCharArray) + " "
    End Function

    ''' <summary>
    ''' Use SELECT condition collection to generate field list to be retrieved in SQL SELECT statement.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SelectString() As String
        Return String.Join(",", Me.SelectCollection).Trim(",".ToCharArray).Replace("'", "\'")
    End Function

    ''' <summary>
    ''' Use Key field value collection to generate filter string to be used within WHERE clause of SQL UPDATE statement.
    ''' This will restrict update to only records with selected key values as defined.
    ''' </summary>
    ''' <returns>Partial SQL string.</returns>
    ''' <remarks></remarks>
    Public Function KeyFilter() As String
        Dim result As String = ""
        For Each field As FieldModel In Me.KeyCollection
            result += field.FilterString + " AND "
        Next
        Return result.Trim("AND ".ToCharArray)
    End Function

    ''' <summary>
    ''' Use AND and OR condition collections to generate a filter string for use in SQL WHERE clause.
    ''' </summary>
    ''' <returns>String for use in SQL WHERE clause</returns>
    ''' <remarks></remarks>
    Public Function RecordFilter() As String
        Dim AndResult As String = ""
        Dim OrResult As String = ""

        For Each field As FieldModel In Me.AndCollection
            AndResult += field.FilterString + " AND "
        Next
        OrResult = OrResult.Trim("OR ".ToCharArray)
        If Me.OrCollection.Count > 1 Then
            OrResult = " (" + OrResult + ") "
        End If

        For Each field As FieldModel In Me.OrCollection
            OrResult += field.FilterString + " OR "
        Next
        AndResult = AndResult.Trim("OR ".ToCharArray)

        Return (AndResult + " AND " + OrResult).Trim("AND ".ToCharArray)
    End Function

#End Region

#Region "Relative SQL"

    ''' <summary>
    ''' Generate SQL INSERT statement. As default, use "Insert into ... (f1, f2, f3, ...) VALUES (v1, v2, v3, ..." syntax.
    ''' </summary>
    ''' <returns>SQL INSERT statement</returns>
    ''' <remarks></remarks>
    Public Function InsertStatement() As String
        Return "INSERT INTO " + Me.TableName + " " + Me.InsertString
    End Function

    ''' <summary>
    ''' Generate SQL SELECT statement with preset AND/OR filter conditions.
    ''' </summary>
    ''' <returns>SQL SELECT statement for records with selected AND/OR filter condition.</returns>
    ''' <remarks></remarks>
    Public Function RecordSetSelectStatement() As String
        If Me.RecordFilter = "" Then
            Return "SELECT " + Me.SelectString + " FROM " + Me.TableName
        Else
            Return "SELECT " + Me.SelectString + " FROM " + Me.TableName + " WHERE " + Me.RecordFilter
        End If
    End Function

    ''' <summary>
    ''' Generte SQL UPDATE statement for records with selected AND/OR conditions.
    ''' As default, use "Update ... SET f1=v1, f2=v2, ..." syntax.
    ''' </summary>
    ''' <returns>SQL UPDATE statement</returns>
    ''' <remarks></remarks>
    Public Function RecordSetUpdateStatement() As String
        If Me.RecordFilter = "" Then
            Return "UPDATE " + Me.TableName + " " + Me.UpdateString
        Else
            Return "UPDATE " + Me.TableName + " " + Me.UpdateString + " WHERE " + Me.RecordFilter
        End If
    End Function

    ''' <summary>
    ''' Generte SQL DELETE statement for records with selected AND/OR conditions.
    ''' </summary>
    ''' <returns>SQL DELETE statement</returns>
    ''' <remarks></remarks>
    Public Function RecordSetDeleteStatement() As String
        If Me.RecordFilter <> "" Then
            Return "DELETE FROM " + Me.TableName + " WHERE " + Me.RecordFilter
        Else
            Return ""
        End If
    End Function

    ''' <summary>
    ''' Generate SQL SELECT statement with preset record KEY filtering conditions.
    ''' </summary>
    ''' <returns>SQL SELECT statement for record with selected key.</returns>
    ''' <remarks></remarks>
    Public Function KeyRecordSelectStatement() As String
        If Me.KeyFilter = "" Then
            Return "SELECT " + Me.SelectString + " FROM " + Me.TableName + " limite 0"
        Else
            Return "SELECT " + Me.SelectString + " FROM " + Me.TableName + " WHERE " + Me.KeyFilter
        End If
    End Function

    ''' <summary>
    ''' Generate SQL UPDATE statement for record with selected key value.
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function KeyRecordUpdateStatement() As String
        If Me.KeyFilter <> "" Then
            Return "UPDATE " + Me.TableName + " " + Me.UpdateString + " WHERE " + Me.KeyFilter
        Else
            Return ""
        End If
    End Function

    ''' <summary>
    ''' Generate SQL DELETE statement for record with selected key value.
    ''' </summary>
    ''' <returns>SQL DELETE statement</returns>
    ''' <remarks></remarks>
    Public Function KeyRecordDeleteStatement() As String
        If Me.KeyFilter <> "" Then
            Return "DELETE FROM " + Me.TableName + " WHERE " + Me.KeyFilter
        Else
            Return ""
        End If
    End Function

#End Region

#Region "Private Function"

    Private Function KeyCollectionByName(ByVal name As String) As FieldModel
        Return Me.KeyCollection.ToList.Find(Function(item) item.Name = name)
    End Function

#End Region

End Class
